"""
用户管理相关接口
@file admin/user_admin.py
@description 处理用户管理相关功能，使用原生SQL
@created 2024-07-18
"""

from django.http import JsonResponse
from django.views.decorators.csrf import csrf_exempt
from django.db import connection
import json
import logging
import datetime
from admin.views import validate_token # Assuming validate_token is in admin/views.py

# 配置日志
logger = logging.getLogger(__name__)

# 默认密码
DEFAULT_PASSWORD = '123456'

def execute_sql(sql, params=None, fetch_one=False, fetch_all=False, get_columns=False, is_insert=False):
    """
    通用SQL执行函数
    """
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql, params)
            if is_insert:
                connection.commit()
                return cursor.lastrowid # For INSERT, return last inserted ID
            if fetch_one:
                row = cursor.fetchone()
                if row and get_columns:
                    columns = [col[0] for col in cursor.description]
                    return dict(zip(columns, row))
                return row
            if fetch_all:
                rows = cursor.fetchall()
                if get_columns:
                    columns = [col[0] for col in cursor.description]
                    return [dict(zip(columns, r)) for r in rows]
                return rows
            connection.commit() # For UPDATE, DELETE
            return cursor.rowcount # For UPDATE/DELETE, return affected rows
    except Exception as e:
        logger.error(f"SQL执行错误: {sql[:100]}... Params: {params} Error: {str(e)}")
        # Rollback in case of error for non-SELECT operations
        if not sql.strip().upper().startswith("SELECT"):
            connection.rollback()
        raise # Re-raise the exception to be caught by the view

def get_user_from_token(request):
    """Helper to get user payload from token"""
    auth_header = request.META.get('HTTP_AUTHORIZATION', '')
    if not auth_header.startswith('Bearer '):
        return None, JsonResponse({'status': 'error', 'message': '未提供有效的令牌'}, status=401)
    
    token = auth_header.split(' ')[1]
    payload = validate_token(token)
    
    if not payload:
        return None, JsonResponse({'status': 'error', 'message': '令牌无效或已过期'}, status=401)
    return payload, None

def is_school_admin(payload):
    """Check if the user is a school_admin"""
    return payload.get('role') == 'school_admin'

@csrf_exempt
def list_users_api(request):
    """
    获取用户列表
    API: GET /api/admin/user/list
    """
    payload, error_response = get_user_from_token(request)
    if error_response:
        return error_response
    
    # 权限检查 (可以根据需要调整，例如是否所有登录管理员都能查看部分用户)
    # if not is_school_admin(payload):
    #     return JsonResponse({'status': 'error', 'message': '权限不足'}, status=403)

    if request.method == 'GET':
        try:
            page = int(request.GET.get('page', 1))
            limit = int(request.GET.get('limit', 10))
            keyword = request.GET.get('keyword', '')
            offset = (page - 1) * limit

            base_query = """
                SELECT u.user_id, u.username, u.real_name, u.role, u.status,
                       u.phone, u.email, u.last_login,
                       yc.name as college_name, ycl.name as class_name,
                       u.college_id, u.class_id
                FROM users u
                LEFT JOIN yzh_colleges yc ON u.college_id = yc.id
                LEFT JOIN yzh_classes ycl ON u.class_id = ycl.id
            """
            count_query = """
                SELECT COUNT(u.user_id) as total
                FROM users u
                LEFT JOIN yzh_colleges yc ON u.college_id = yc.id
                LEFT JOIN yzh_classes ycl ON u.class_id = ycl.id
            """
            
            where_clauses = []
            params = []

            if keyword:
                keyword_like = f"%{keyword}%"
                where_clauses.append("(u.username LIKE %s OR u.real_name LIKE %s OR yc.name LIKE %s OR ycl.name LIKE %s)")
                params.extend([keyword_like, keyword_like, keyword_like, keyword_like])
            
            # 根据实际需求，可以限制只查询特定角色的用户，如管理员或所有用户
            # where_clauses.append("u.role IN ('school_admin', 'college_admin', 'class_admin', 'teacher', 'student')")


            if where_clauses:
                where_sql = " WHERE " + " AND ".join(where_clauses)
                base_query += where_sql
                count_query += where_sql
            
            base_query += " ORDER BY u.user_id DESC LIMIT %s OFFSET %s"
            params.extend([limit, offset])

            users = execute_sql(base_query, params, fetch_all=True, get_columns=True)
            total_count_result = execute_sql(count_query, params[:-2], fetch_one=True) # Exclude limit and offset for count
            total = total_count_result[0] if total_count_result else 0
            
            # 格式化 last_login
            for user in users:
                if user.get('last_login') and isinstance(user['last_login'], datetime.datetime):
                    user['last_login'] = user['last_login'].strftime('%Y-%m-%d %H:%M:%S')
                else:
                    user['last_login'] = None

                
                return JsonResponse({
                    'status': 'success',
                'data': {
                    'list': users,
                    'total': total,
                    'page': page,
                    'limit': limit
                }
                })
        except Exception as e:
            logger.error(f"获取用户列表失败: {str(e)}")
            return JsonResponse({'status': 'error', 'message': f'服务器内部错误: {str(e)}'}, status=500)
    return JsonResponse({'status': 'error', 'message': '仅支持GET请求'}, status=405)


@csrf_exempt
def add_user_api(request):
    """
    添加用户
    API: POST /api/admin/user/add
    """
    payload, error_response = get_user_from_token(request)
    if error_response:
        return error_response
    
    if not is_school_admin(payload): # 只有校级管理员可以添加用户
        return JsonResponse({'status': 'error', 'message': '权限不足'}, status=403)

    if request.method == 'POST':
        try:
            data = json.loads(request.body)
            username = data.get('username')
            password = data.get('password', DEFAULT_PASSWORD) # 使用默认密码
            real_name = data.get('realName')
            role = data.get('role')
            college_id = data.get('collegeId')
            class_id = data.get('classId') # 可能为 None
            phone = data.get('phone')
            email = data.get('email')
            status = data.get('status', 1) # 默认为启用
            
            # 验证必填字段
            required_fields = {'username': username, 'realName': real_name, 'role': role}
            for field, value in required_fields.items():
                if not value:
                    return JsonResponse({'status': 'error', 'message': f'缺少必填字段: {field}'}, status=400)

            # 根据角色验证其他字段
            if role == 'college_admin' and not college_id:
                return JsonResponse({'status': 'error', 'message': '学院管理员必须关联学院'}, status=400)
            if role == 'class_admin' and (not college_id or not class_id):
                return JsonResponse({'status': 'error', 'message': '班级管理员必须关联学院和班级'}, status=400)
            
            # 检查用户名是否已存在
            check_sql = "SELECT user_id FROM users WHERE username = %s"
            if execute_sql(check_sql, [username], fetch_one=True):
                return JsonResponse({'status': 'error', 'message': '用户名已存在'}, status=400)

            insert_sql = """
                INSERT INTO users (username, password, real_name, role, college_id, class_id, phone, email, status)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                RETURNING user_id
            """
            # 注意：密码直接存储，不加密
            params = [
                username, password, real_name, role, 
                college_id if college_id else None, 
                class_id if class_id else None, 
                phone, email, status
            ]
            
            new_user_id = execute_sql(insert_sql, params, is_insert=True)
            return JsonResponse({
                'status': 'success',
                'message': '添加用户成功',
                'data': {'id': new_user_id, 'username': username, 'role': role}
            }, status=201)
                
        except json.JSONDecodeError:
            return JsonResponse({'status': 'error', 'message': '无效的JSON数据'}, status=400)
        except Exception as e:
            logger.error(f"添加用户失败: {str(e)}")
            return JsonResponse({'status': 'error', 'message': f'服务器内部错误: {str(e)}'}, status=500)
    return JsonResponse({'status': 'error', 'message': '仅支持POST请求'}, status=405)

@csrf_exempt
def update_user_api(request, user_id):
    """
    更新用户信息
    API: PUT /api/admin/user/update/{user_id}
    """
    payload, error_response = get_user_from_token(request)
    if error_response:
        return error_response

    if not is_school_admin(payload):
        return JsonResponse({'status': 'error', 'message': '权限不足'}, status=403)
    
    if request.method == 'PUT':
        try:
            # 检查用户是否存在
            check_sql = "SELECT role FROM users WHERE user_id = %s"
            user_to_update = execute_sql(check_sql, [user_id], fetch_one=True, get_columns=True)
            if not user_to_update:
                return JsonResponse({'status': 'error', 'message': '用户不存在'}, status=404)

            data = json.loads(request.body)
            real_name = data.get('realName')
            role = data.get('role')
            college_id = data.get('collegeId')
            class_id = data.get('classId')
            phone = data.get('phone')
            email = data.get('email')
            status = data.get('status')
                
            # 不能修改校级管理员的角色或禁用校级管理员 (除非是其本人，但前端通常会禁止)
            if user_to_update['role'] == 'school_admin' and user_id != payload.get('user_id'): # 确保不是修改自己
                 if role != 'school_admin' : # 试图修改校级管理员的角色
                     return JsonResponse({'status': 'error', 'message': '不能修改校级管理员的角色'}, status=403)
                 if status == 0: # 试图禁用其他校级管理员
                     return JsonResponse({'status': 'error', 'message': '不能禁用其他校级管理员账户'}, status=403)


            update_sql = """
                UPDATE users
                SET real_name = %s, role = %s, college_id = %s, class_id = %s,
                    phone = %s, email = %s, status = %s
                WHERE user_id = %s
            """
            params = [
                real_name, role, 
                college_id if college_id else None, 
                class_id if class_id else None, 
                phone, email, status, 
                user_id
            ]
            
            affected_rows = execute_sql(update_sql, params)

            if affected_rows > 0:
                return JsonResponse({'status': 'success', 'message': '更新用户成功'})
            else:
                # 可能因为数据未变动或用户不存在（虽然前面检查过）
                return JsonResponse({'status': 'info', 'message': '数据未变动或用户不存在'})


        except json.JSONDecodeError:
            return JsonResponse({'status': 'error', 'message': '无效的JSON数据'}, status=400)
        except Exception as e:
            logger.error(f"更新用户(ID: {user_id})失败: {str(e)}")
            return JsonResponse({'status': 'error', 'message': f'服务器内部错误: {str(e)}'}, status=500)
    return JsonResponse({'status': 'error', 'message': '仅支持PUT请求'}, status=405)


@csrf_exempt
def delete_user_api(request, user_id):
    """
    删除用户
    API: DELETE /api/admin/user/delete/{user_id}
    """
    payload, error_response = get_user_from_token(request)
    if error_response:
        return error_response

    if not is_school_admin(payload):
        return JsonResponse({'status': 'error', 'message': '权限不足'}, status=403)

    if request.method == 'DELETE':
        try:
            # 检查用户是否存在及角色
            check_sql = "SELECT role FROM users WHERE user_id = %s"
            user_to_delete = execute_sql(check_sql, [user_id], fetch_one=True, get_columns=True)
                
            if not user_to_delete:
                return JsonResponse({'status': 'error', 'message': '用户不存在'}, status=404)

            if user_to_delete['role'] == 'school_admin':
                return JsonResponse({'status': 'error', 'message': '不能删除校级管理员账户'}, status=403)
            
            # 检查是否有关联数据需要处理 (示例，实际项目中可能需要更复杂的逻辑)
            # e.g., if user is a teacher with courses, or student with grades, etc.

            delete_sql = "DELETE FROM users WHERE user_id = %s"
            affected_rows = execute_sql(delete_sql, [user_id])

            if affected_rows > 0:
                return JsonResponse({'status': 'success', 'message': '删除用户成功'})
            else:
                return JsonResponse({'status': 'error', 'message': '删除用户失败，用户可能已被删除'}, status=404) # Should have been caught by check

        except Exception as e:
            logger.error(f"删除用户(ID: {user_id})失败: {str(e)}")
            return JsonResponse({'status': 'error', 'message': f'服务器内部错误: {str(e)}'}, status=500)
    return JsonResponse({'status': 'error', 'message': '仅支持DELETE请求'}, status=405)

@csrf_exempt
def reset_password_api(request, user_id):
    """
    重置用户密码
    API: POST /api/admin/user/reset-password/{user_id}
    """
    payload, error_response = get_user_from_token(request)
    if error_response:
        return error_response

    if not is_school_admin(payload):
        return JsonResponse({'status': 'error', 'message': '权限不足'}, status=403)

    if request.method == 'POST':
        try:
            # 检查用户是否存在
            check_sql = "SELECT user_id FROM users WHERE user_id = %s"
            if not execute_sql(check_sql, [user_id], fetch_one=True):
                return JsonResponse({'status': 'error', 'message': '用户不存在'}, status=404)

            # 注意：密码直接存储，不加密
            reset_sql = "UPDATE users SET password = %s WHERE user_id = %s"
            execute_sql(reset_sql, [DEFAULT_PASSWORD, user_id])
                
            return JsonResponse({'status': 'success', 'message': '密码重置成功，新密码为：' + DEFAULT_PASSWORD})
        except Exception as e:
            logger.error(f"重置用户(ID: {user_id})密码失败: {str(e)}")
            return JsonResponse({'status': 'error', 'message': f'服务器内部错误: {str(e)}'}, status=500)
    return JsonResponse({'status': 'error', 'message': '仅支持POST请求'}, status=405)

@csrf_exempt
def toggle_user_status_api(request, user_id):
    """
    切换用户状态 (启用/禁用)
    API: PUT /api/admin/user/toggle-status/{user_id}
    """
    payload, error_response = get_user_from_token(request)
    if error_response:
        return error_response

    if not is_school_admin(payload):
        return JsonResponse({'status': 'error', 'message': '权限不足'}, status=403)

    if request.method == 'PUT':
        try:
            data = json.loads(request.body)
            new_status = data.get('status')

            if new_status not in [0, 1]: # 0 for disabled, 1 for enabled
                return JsonResponse({'status': 'error', 'message': '无效的状态值'}, status=400)

            # 检查用户是否存在及角色
            check_sql = "SELECT role FROM users WHERE user_id = %s"
            user_to_toggle = execute_sql(check_sql, [user_id], fetch_one=True, get_columns=True)

            if not user_to_toggle:
                return JsonResponse({'status': 'error', 'message': '用户不存在'}, status=404)

            if user_to_toggle['role'] == 'school_admin' and new_status == 0:
                 # Prevent disabling a school admin, unless it's the admin disabling themselves (which is unlikely via this API)
                 # More robust check: if payload.get('user_id') != user_id and user_to_toggle['role'] == 'school_admin'
                return JsonResponse({'status': 'error', 'message': '不能禁用校级管理员账户'}, status=403)
            
            toggle_sql = "UPDATE users SET status = %s WHERE user_id = %s"
            execute_sql(toggle_sql, [new_status, user_id])

            action = "启用" if new_status == 1 else "禁用"
            return JsonResponse({'status': 'success', 'message': f'用户状态已成功{action}'})
                
        except json.JSONDecodeError:
            return JsonResponse({'status': 'error', 'message': '无效的JSON数据'}, status=400)
        except Exception as e:
            logger.error(f"切换用户(ID: {user_id})状态失败: {str(e)}")
            return JsonResponse({'status': 'error', 'message': f'服务器内部错误: {str(e)}'}, status=500)
    return JsonResponse({'status': 'error', 'message': '仅支持PUT请求'}, status=405)


# --- 学院和班级相关的API ---

@csrf_exempt
def list_colleges_api(request):
    """
    获取学院列表
    API: GET /api/admin/colleges/list
    """
    # 通常学院列表对登录用户可见，无需严格的school_admin权限
    payload, error_response = get_user_from_token(request)
    if error_response: # Still need a valid token
        return error_response

    if request.method == 'GET':
        try:
            sql = "SELECT id, name, description FROM yzh_colleges WHERE status = 1 ORDER BY name"
            colleges = execute_sql(sql, fetch_all=True, get_columns=True)
            return JsonResponse({'status': 'success', 'data': colleges})
        except Exception as e:
            logger.error(f"获取学院列表失败: {str(e)}")
            return JsonResponse({'status': 'error', 'message': f'服务器内部错误: {str(e)}'}, status=500)
    return JsonResponse({'status': 'error', 'message': '仅支持GET请求'}, status=405)

@csrf_exempt
def list_classes_by_college_api(request, college_id):
    """
    根据学院ID获取班级列表
    API: GET /api/admin/colleges/{college_id}/classes
    """
    payload, error_response = get_user_from_token(request)
    if error_response:
        return error_response

    if request.method == 'GET':
        try:
            # 确保 college_id 是整数
            try:
                college_id_int = int(college_id)
            except ValueError:
                return JsonResponse({'status': 'error', 'message': '无效的学院ID格式'}, status=400)

            sql = "SELECT id, name, grade_year FROM yzh_classes WHERE college_id = %s AND status = 1 ORDER BY name"
            classes = execute_sql(sql, [college_id_int], fetch_all=True, get_columns=True)
            # 前端 UserManagement.vue 的 getClassesByCollege API 调用期望 grade_year 作为 gradeId
            # 所以在SQL查询中可以 SELECT grade_year as gradeId，或者在这里转换
            formatted_classes = [{'id': c['id'], 'name': c['name'], 'gradeId': c['grade_year']} for c in classes]

            return JsonResponse({'status': 'success', 'data': formatted_classes})
        except Exception as e:
            logger.error(f"获取班级列表(学院ID: {college_id})失败: {str(e)}")
            return JsonResponse({'status': 'error', 'message': f'服务器内部错误: {str(e)}'}, status=500)
    return JsonResponse({'status': 'error', 'message': '仅支持GET请求'}, status=405)

# 移除旧的 user_list 和 user_detail 函数，因为它们的功能已被新的API函数覆盖和重构
# def user_list(request): ...
# def user_detail(request, user_id): ...
# def get_user_permissions(request, username=None): ...
# def get_permissions_by_role(role): ...
# 这两个权限相关的函数暂时不实现，因为我们主要关注用户表的直接操作和校级管理员的权限控制。
# 如果需要更细致的基于permission表的权限管理，可以后续添加。 